create database TestDB
on
(
name='TestDB',
filename='D:\text\TestDB.mdf',
size=5MB,
maxsize=50MB,
filegrowth=10%
)
log on
(
name='TestDB_log',
filename='D:\text\TestDB_log.mdf',
size=5MB,
maxsize=50MB,
filegrowth=10%
)
go

use TestDB
go

create table typeInfo
(
typeId int primary key identity(1,1),
typeName varchar(10) not null
)
go

create table loginInfo
(
LoginId int primary key identity(1,1),
LoginName nvarchar(10) not null unique,
LoginPwd nvarchar(20) not null default('123456'),
LoginSex nvarchar(1),
Loginbirthday datetime,
Logintype text
)
go

create database company
on
(
name='company',
filename='D:\text\company.mdf',
size=5MB,
maxsize=50MB,
filegrowth=10%
)
log on
(
name='company_log',
filename='D:\text\company_log.mdf',
size=5MB,
maxsize=50MB,
filegrowth=10%
)
go

use company
go

create table sectionInfo
(
sectionID int primary key identity(1,1),
sectionName varchar(10) not null
)
go

create table userInfo
(
userNo int primary key identity(1,1) not null,
userName varchar(10) unique not null check(len(userName)>=4),
userSex varchar(2)check(userSex=''or userSex='Ů')not null,
userAge int not null check(userAge>=1 and userAge<=100),
userAddress varchar(50) default(''),
userSection  int references sectionInfo(sectionID)
)
go

create table workInfo
(
workId int primary key identity(1,1) not null,
userId int references userInfo(userNo) not null,
workTime datetime not null,
workState nvarchar(2) not null check(workState='ٵ' or workState='' or workState='' or workState='' or workState='¼')
)
go

create database classInfo
on
(
name='classInfo',
filename='D:\text\classInfo.mdf',
size=5MB,
maxsize=50MB,
filegrowth=10%
)
log on
(
name='classInfo_log',
filename='D:\text\classInfo_log.mdf',
size=5MB,
maxsize=50MB,
filegrowth=10%
)
go

use classInfo

create table classInfo
(
classid int primary key identity(1,1),
classname nvarchar(10) not null unique,
classtime datetime not null,
classdescribe text,
)
go

create table stuInfo
(
stuid int primary key identity(1,1),
stuname nvarchar(2) not null,
stusex nchar(1) default('') check(stusex='' or stusex='Ů'),
stuage int check(stuage>=15 and stuage<=40) not null,
stusite nvarchar(4) default('人'),
classid int references classInfo(classid)
)
go

create table course
(
courseid int primary key identity(1,1),
coursename nvarchar(10) not null unique,
coursedescribe text
)
go

create table grade
(
gradeid int primary key identity(1,1), 
stuid int not null,
courseid int not null,
grade int check(grade>=0 and grade<=100)
)
go

create database house
on
(
name='house',
filename='D:\text\house.mdf',
size=5MB,
maxsize=50MB,
filegrowth=10%
)
log on
(
name='house_log',
filename='D:\text\house_log.mdf',
size=5MB,
maxsize=50MB,
filegrowth=10%
)
go

use house

create table tblUser
(
userId int primary key identity(1,1),
userName nvarchar(10),
userTel int
)
go

create table tblHouseType
(
typeId int primary key identity(1,1),
typName nvarchar(10)
)
go

create table tblQx 
(
qxId int primary key identity(1,1),
qxName nvarchar(10)
)
go

create table tblHouseInfo
(
id int primary key identity(1,1),
[desc] text,
userId int references tblUser(userId),
zj money,
shi int,
ting int,
typeId int references tblHouseType(typeId),
qxId int references tblQx(qxId)
)
go